import pandas as pd
import numpy as np
import os
import pymysql
from datetime import datetime

class daily_update():
    def __init__(self):
        self.host = '192.168.1.145'
        self.port = 3306
        self.user = 'root'
        self.password = '280303'
        self.db = 'daily_feature'
        self.charset = "utf8"


    def connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.db,
                                    charset=self.charset)


    def execute_and_disconnect(self):
        self.conn.commit()
        self.conn.close()


    def exec_sql(self, sql, value=()):
        cur = self.conn.cursor()
        cur.execute(sql, value)
        res = cur.fetchall()
        cur.close()
        return res


    def basic_update(self, y_name, y_path):
        select_y_name_sql = "select count(*) from information_schema.tables where table_schema='" + self.db + "' and table_name=%s"
        select_y_name_value = (y_name)
        res = self.exec_sql(select_y_name_sql, select_y_name_value)
        if res[0][0] == 0:
            create_table_sql = "create table `" + y_name + "` as select * from `服务业生产指数_当月同比` where 1=2"
            self.exec_sql(create_table_sql)
            print('新表\"' + y_name + '\"已经新增')

        data = pd.read_excel(y_path, index_col=0)
        data['time'] = data.index
        data['upload_value'] = data.iloc[:, 0]
        data['history_best_value'] = data.iloc[:, 1]
        data = data[['time', 'upload_value', 'history_best_value']]

        check_table_sql = "select count(*) from `" + y_name + "`"
        res = self.exec_sql(check_table_sql)

        if res[0][0] != 0:
            delete_table_sql = "delete from `" + y_name + "`"
            self.exec_sql(delete_table_sql)

        for i in range(data.shape[0]):
            update_table_sql = "insert into `" + y_name + "` values (%s, %s, %s, %s)"
            update_table_value = (int(i+1), data['time'][i].to_pydatetime(), float(data['upload_value'][i]),
                                  float(data['history_best_value'][i]))
            self.exec_sql(update_table_sql, update_table_value)

    def update(self, y_name_list, y_path_list):
        self.connect()
        for i in range(len(y_name_list)):
            y_name = y_name_list[i]
            y_path = y_path_list[i]
            self.basic_update(y_name, y_path)
            print('\"' + y_name + '\"更新完毕')
        self.execute_and_disconnect()


    def basic_automatically_update(self, y_name, y_path):
        select_y_name_sql = "select count(*) from information_schema.tables where table_schema='" + self.db + "' and table_name=%s"
        select_y_name_value = (y_name)
        res = self.exec_sql(select_y_name_sql, select_y_name_value)
        if res[0][0] == 0:
            create_table_sql = "create table `" + y_name + "` as select * from `服务业生产指数_当月同比` where 1=2"
            self.exec_sql(create_table_sql)
            print('新表\"' + y_name + '\"已经新增')

        data = pd.read_excel(y_path, index_col=0)
        data['time'] = data.index
        data['upload_value'] = data.iloc[:, 0]
        data['history_best_value'] = data.iloc[:, 0]
        data = data[['time', 'upload_value', 'history_best_value']]

        for i in range(data.shape[0]):
            check_exist_sql = "select count(*) from `" + y_name + "` where time=%s"
            check_exist_value = (data['time'][i].to_pydatetime())
            res = self.exec_sql(check_exist_sql, check_exist_value)
            if res[0][0] != 0:
                delete_line_sql = "delete from `" + y_name + "` where time=%s"
                delete_line_value = (data['time'][i].to_pydatetime())
                self.exec_sql(delete_line_sql, delete_line_value)

            update_table_sql = "insert into `" + y_name + "` values (%s, %s, %s, %s)"
            update_table_value = (data['time'][i].to_pydatetime().timetuple().tm_yday + 1096,
                                  data['time'][i].to_pydatetime(), float(data['upload_value'][i]),
                                  float(data['history_best_value'][i]))
            self.exec_sql(update_table_sql, update_table_value)

    def automatically_update(self, automatic_y_path):
        dirs = os.listdir(automatic_y_path)
        self.connect()
        for dir in dirs:
            y_name = "_".join(dir.split("_")[1:])
            dir_path = automatic_y_path + os.path.sep + dir
            y_path = dir_path + os.path.sep + y_name + '_result.xlsx'
            self.basic_automatically_update(y_name, y_path)
            print('\"' + y_name + '\"自动更新完毕')
        self.execute_and_disconnect()


    def select_all(self):
        self.connect()
        select_table_sql = "select table_name from information_schema.tables where table_schema='daily_feature'"
        table_names = self.exec_sql(select_table_sql)
        # table_names = table_names[2:4] + table_names[9:10]
        res_dict = {}
        for i in range(len(table_names)):
            table_name = table_names[i][0]
            table_name = table_name.upper()
            res_dict[table_name] = {}

            select_time_sql = "select time from `" + table_name + "`"
            times = self.exec_sql(select_time_sql)
            select_value_sql = "select upload_value from `" + table_name + "`"
            values = self.exec_sql(select_value_sql)
            for j in range(len(times)):
                time = times[j][0]
                value = values[j][0]
                res_dict[table_name][time] = value
        self.execute_and_disconnect()
        res_dataframe = pd.DataFrame(res_dict)

        return res_dataframe


if __name__ == "__main__":
    y_name_list = ['社融规模存量_当月同比','M2_当月同比'][:1]
    y_path_list = ['C:/Users/GPU_Tjdhf/Desktop/更新指标/社融规模存量_当月同比.xlsx','C://Users//GPU_Tjdhf//Desktop//更新指标//M2//M2.xlsx'][:1]
    # automatic_y_path = 'C:/2021_04_07/input/test_for_new_monitor'

    Update = daily_update()
    Update.update(y_name_list, y_path_list)
    # Update.automatically_update(automatic_y_path)
    # result = Update.select_all()
    # result.to_excel("D:/result.xlsx")









